package com.scalar.chiptrack.engineering.actions;

import com.scalar.chiptrack.ChipTrackAction;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.SessionKeys;
import com.scalar.chiptrack.operations.process.dao.ProductDao;
import com.scalar.chiptrack.operations.process.Upload;
import com.scalar.chiptrack.operations.release.AttachFile;
import com.scalar.chiptrack.operations.release.ReleaseHelper;
import com.scalar.chiptrack.engineering.forms.CummulativeYieldForm;
import com.scalar.chiptrack.engineering.CummulativeYieldInfo;
import com.scalar.chiptrack.engineering.ExcelLotInfo;
import com.scalar.chiptrack.engineering.LotYieldConstants;
import com.scalar.chiptrack.engineering.LotYieldInfo;
import com.scalar.chiptrack.engineering.dao.CummYieldDAO;
import com.scalar.ScalarException;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionForm;
import org.apache.struts.util.LabelValueBean;
import org.apache.struts.upload.FormFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;

import jxl.write.*;
import jxl.write.Number;
import jxl.Cell;
import jxl.Workbook;
import jxl.CellType;
import jxl.Sheet;
import jxl.read.biff.BiffException;

public class CummulativeYieldReportAction extends ChipTrackAction
{
    public ActionForward execute ( ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response ) throws Exception
    {
        ActionForward actionForward     = null;
        String action                   = request.getParameter( "action" );
        HashMap errors                  = new HashMap();
        HashMap info                    = new HashMap();        
        CummulativeYieldForm yieldForm  = ( CummulativeYieldForm ) form;
        ArrayList baseProductsList      = null;
        ArrayList fileInfoList          = new ArrayList();
        LabelValueBean lvb              = null;
        CummulativeYieldInfo fileInfo   = null;
        int baseProductId               = 0;
        String baseProductName          = null;

        request.setAttribute( SessionKeys.INFO_MESSAGES, info );
        request.setAttribute( SessionKeys.ERROR_MESSAGES, errors );
        
        try
        {
            if( Constants.ACTION_UPLOAD.equalsIgnoreCase( action ) )
            {
                baseProductId   = yieldForm.getBaseProductId();
                baseProductName = ProductDao.getBaseProductName( baseProductId + "" );
                yieldForm.setBaseProductName( baseProductName );
                try
                {
                    uploadCummulativeReportYieldFiles( baseProductName, yieldForm.getAttachmentFile() );

                    CummYieldDAO.updateUploadInformation( baseProductId );

                    info.put( "Cummulative Yield File related to Base Product " + baseProductName + " uploaded successfully", null );
                }
                catch( Exception e )
                {
                    e.printStackTrace();
                    errors.put ( "Internal Server Error", "Could not upload Cummulative Yield Report File" );
                }
            }
            else if( Constants.ACTION_PROCESS.equalsIgnoreCase( action ) )
            {
                String baseProductIdStr = request.getParameter( "baseProductId" );
                int baseProductIdInt    = 0;
                try
                {
                    baseProductIdInt = Integer.parseInt( baseProductIdStr );    
                }catch( NumberFormatException nfe ) {};
                try
                {
                    baseProductName = ProductDao.getBaseProductName( baseProductIdStr );

                    saveAsCummulativeReportYieldFiles( baseProductName );
                    
                    updateChipTracLotDetailsIntoYieldReportSheet( baseProductIdInt, baseProductName );

                    CummYieldDAO.updateYieldFileProcessInformation( baseProductIdInt );

                    info.put( "Information into Cummulative Yield File for Base Product " + baseProductName + " successfully processed", null );
                }
                catch( Exception e )
                {
                    e.printStackTrace();
                    errors.put ( "Problem in uploading the information into Cummulative Yield Report File", null );
                }    
            }

            baseProductsList = ProductDao.loadChipBaseProducts( null );

            if( baseProductsList != null && baseProductsList.size() > 0 )
            {
                for( int i = 0; i < baseProductsList.size(); i++ )
                {
                    lvb = ( LabelValueBean ) baseProductsList.get( i );

                    fileInfo = new CummulativeYieldInfo();
                    try
                    {
                        fileInfo.setBaseProductId( Integer.parseInt( lvb.getValue() ) );
                    }catch( NumberFormatException nfe ){};

                    fileInfo.setBaseProductName( lvb.getLabel() );
                    fileInfo.setAttachFile( retrieveAttachedFileInformation( fileInfo.getBaseProductName() ) );

                    CummYieldDAO.loadYieldFileUploadAndProcessedDetails( null, fileInfo.getBaseProductId(), fileInfo );
                    
                    fileInfoList.add( fileInfo );
                }
            }

            yieldForm.setCummulativeFileInfoList( fileInfoList );

            request.setAttribute( "baseProductList", baseProductsList );
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }

        return mapping.findForward( "cummulativeYieldReport" );
    }

    private static AttachFile retrieveAttachedFileInformation( String baseProductName ) throws ScalarException
    {
        AttachFile attachFile       = new AttachFile();
        String filePerfixLocation   = ConfigManager.getCummulativeYieldReportFileDir();
        String fileWebLocation      = ConfigManager.getCummulativeYieldReportFileWebDir();
        File file                   = null;
        try
        {
            String fileWebAttachment    = fileWebLocation  + baseProductName + "/";
            String fileAttachment       = filePerfixLocation  + baseProductName + "/";
            String attachmentFileName   = ReleaseHelper.getSpecFileAttachment( fileAttachment );

            file = new File ( fileAttachment + attachmentFileName );
            if( file == null || file.exists() == false || file.isFile() == false )
            {
                attachFile.setFileName( null );
                attachFile.setFileNameWithWebPath( null );
            }
            else
            {
                attachFile.setFileName( attachmentFileName );
                attachFile.setFileNameWithPath( fileAttachment + attachmentFileName );
                attachFile.setFileNameWithWebPath( fileWebAttachment + attachmentFileName );
            }
        }
        catch( SecurityException se ) { se.printStackTrace(); }
        finally
        {
            if( file != null )
            {
                file = null;
            }
        }

        return attachFile;
    }

    private static void uploadCummulativeReportYieldFiles( String baseProductName, FormFile formFile ) throws ScalarException
    {
        String dateTime            = StringUtils.formatDateToString( new Date(), "MM-dd-yyyy-HH-mm-ss");
        String filePerfixLocation  = ConfigManager.getCummulativeYieldReportFileDir() + "/";
        String prvFile             = "";
        boolean success            = false;
        File file                  = null;
        File newFile               = null;
        try
        {
            file = new File( filePerfixLocation + baseProductName );
            if( file.exists() == false )
            {
                file.mkdirs();
                success = true;
            }
            else
            {
                prvFile = ReleaseHelper.getSpecFileAttachment( filePerfixLocation + baseProductName + "/" );
                if( prvFile != null && prvFile.trim().length() > 0 )
                {
                    file = new File( filePerfixLocation + baseProductName + "/" + dateTime + "/" );
                    if( file.exists() == false )
                    {
                        file.mkdirs();
                    }
                    //Upload.fileAsSave( ( filePerfixLocation + baseProductId + "/" + prvFile ), ( filePerfixLocation + baseProductId + "/" + dateTime + "/" + prvFile ) );
                    file    = new File( filePerfixLocation + baseProductName + "/" + prvFile );
                    newFile = new File( filePerfixLocation + baseProductName + "/" + dateTime + "/" + prvFile );
                    success = file.renameTo( newFile );
                }
            }
        }
        catch( SecurityException se ) { }

        try
        {
            if( success  )
            {
                Upload.loadFile( ( filePerfixLocation + baseProductName + "/" ), formFile );
            }
            else
            {
                throw new ScalarException( "Problem in uploading the Cummulative Yield Report File" );
            }
        }
        catch( SecurityException se ) { }
        finally
        {
            if( file != null )
            {
                file = null;
            }
            if( newFile != null )
            {
                newFile = null;
            }
        }
    }

    private static void saveAsCummulativeReportYieldFiles( String baseProductName ) throws ScalarException
    {
        String dateTime            = StringUtils.formatDateToString( new Date(), "MM-dd-yyyy-HH-mm-ss");
        String filePerfixLocation  = ConfigManager.getCummulativeYieldReportFileDir() + "/";
        String prvFile             = "";
        File file                  = null;
        File newFile               = null;
        try
        {
            file = new File( filePerfixLocation + baseProductName );
            if( file.exists() == false )
            {
                file.mkdirs();
            }
            else
            {
                prvFile = ReleaseHelper.getSpecFileAttachment( filePerfixLocation + baseProductName + "/" );
                if( prvFile != null && prvFile.trim().length() > 0 )
                {
                    file = new File( filePerfixLocation + baseProductName + "/" + dateTime + "/" );
                    if( file.exists() == false )
                    {
                        file.mkdirs();
                    }
                    Upload.fileAsSave( ( filePerfixLocation + baseProductName + "/" + prvFile ), ( filePerfixLocation + baseProductName + "/" + dateTime + "/" + prvFile ) );
                }
            }
        }
        catch( SecurityException se ) { }
        finally
        {
            if( file != null )
            {
                file = null;
            }
            if( newFile != null )
            {
                newFile = null;
            }
        }
    }


    private static void updateChipTracLotDetailsIntoYieldReportSheet( int baseProductId, String baseProductName ) throws ScalarException
    {
        HashMap lotYieldInfoMap             = null;
        LotYieldInfo lotYieldInfo           = null;
        ArrayList rowsList                  = null;

        File sourceFile                             = null;
        WritableWorkbook writableWorkbook           = null;
        WritableSheet writableSheet                 = null;
        WritableCellFormat writableCellFormat       = null;
        Cell[] rowWithCells                         = null;
        Label label                                 = null;
        Number number                               = null;
        DateTime date                               = null;
        int currentRowNo                            = 0;
        int lastRowNo                               = 0;

        Workbook sourceWorkbook                     = null;
		DateFormat dateFormat                       = new DateFormat( "MM/dd/yy" );
        SimpleDateFormat sdf                        = new SimpleDateFormat("MM/dd/yy");

        AttachFile attachedFile                     = null;

        try
        {
            try
            {
                lotYieldInfoMap = CummYieldDAO.loadLotYieldDetails( null, baseProductId );

                rowsList = loadExcelLotDetails( baseProductName );

                if( rowsList != null && rowsList.size() > 0 )
                {
                    lastRowNo = ( ( ExcelLotInfo ) rowsList.get( rowsList.size() - 1 ) ).getStartingRow() + 1;
                }

                mergetNewLots( lotYieldInfoMap, rowsList );
            }
            catch (ScalarException e)
            {
                e.printStackTrace();
            }

            attachedFile = retrieveAttachedFileInformation( baseProductName );

            sourceFile = new File( attachedFile.getFileNameWithPath() );

            sourceWorkbook      = Workbook.getWorkbook( sourceFile );
            writableWorkbook    = Workbook.createWorkbook( sourceFile, sourceWorkbook );
            writableSheet       = writableWorkbook.getSheet( 0 );

            if( rowsList != null && rowsList.size() > 0 )
            {
                ExcelLotInfo excelLotInfo1 = null;

                //System.out.println("------------------ rowsList.size() = " + rowsList.size() + " Lot No = " + ( ( ExcelLotInfo ) rowsList.get( rowsList.size() - 1 ) ).getLotNumber() + " LastRowNo = " + lastRowNo );

                for( int i = 0; i < rowsList.size(); i++  )
                {
                    excelLotInfo1 = ( ExcelLotInfo ) rowsList.get(i);

                    currentRowNo = excelLotInfo1.getStartingRow();

                    if( excelLotInfo1.isNewLotNumber() )
                    {
                        currentRowNo = lastRowNo;
                        lastRowNo = lastRowNo + 1;
                    }

                    //System.out.println(" i = " + i + "  = " + excelLotInfo1.getLotNumber() + " current Row No = " + currentRowNo + " Starting Row = " + excelLotInfo1.getStartingRow() + " Ending Row = " + excelLotInfo1.getEndingRow() +  " isNewLotNumber = " + excelLotInfo1.isNewLotNumber() + " lastRowNo " + lastRowNo );
                    if( lotYieldInfoMap.containsKey( excelLotInfo1.getLotNumber() ) )
                    {
                        lotYieldInfo = ( LotYieldInfo ) lotYieldInfoMap.get( excelLotInfo1.getLotNumber() );

                        rowWithCells = writableSheet.getRow( currentRowNo );

                        //System.out.println("-------Action - Lot No = " + lotYieldInfo.getWaferLotNo() + " WaferSortYield = " + lotYieldInfo.getWaferSortYield() + " rowWithCells.length = " + rowWithCells.length + " isNewLotNumber() = "  + excelLotInfo1.isNewLotNumber() );

                        if( rowWithCells.length >= LotYieldConstants.WAFER_LOT_NO_INDEX && rowWithCells[ LotYieldConstants.WAFER_LOT_NO_INDEX ].getType().equals( CellType.EMPTY ) )
                        {
                            label = new Label( LotYieldConstants.WAFER_LOT_NO_INDEX, currentRowNo, lotYieldInfo.getWaferLotNo() );
                            writableSheet.addCell( label );
                            if( excelLotInfo1.isNewLotNumber() )
                            {
                                writableSheet.setRowView( currentRowNo, 25*20 );
                            }
                        }
                        if( rowWithCells.length >= LotYieldConstants.FAB_OUT_WAFERS_INDEX && rowWithCells[LotYieldConstants.FAB_OUT_WAFERS_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getFabOutWafer() > 0 )
                        {
                            number = new Number( LotYieldConstants.FAB_OUT_WAFERS_INDEX, currentRowNo, lotYieldInfo.getFabOutWafer() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.DIE_REV_INDEX && rowWithCells[LotYieldConstants.DIE_REV_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getDieRev() > 0 )
                        {
                            number = new Number( LotYieldConstants.DIE_REV_INDEX, currentRowNo, lotYieldInfo.getDieRev() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.WAFER_OUTDATE_INDEX && rowWithCells[LotYieldConstants.WAFER_OUTDATE_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getWaferOutDate() != null )
                        {
                            writableCellFormat = new WritableCellFormat( dateFormat );
                            writableCellFormat.setVerticalAlignment( jxl.format.VerticalAlignment.CENTRE );
                            writableCellFormat.setAlignment( jxl.format.Alignment.CENTRE );
                            date = new DateTime( LotYieldConstants.WAFER_OUTDATE_INDEX, currentRowNo, sdf.parse( lotYieldInfo.getWaferOutDate() ), writableCellFormat );
                            writableSheet.addCell( date );

                        }
                        if( rowWithCells.length >= LotYieldConstants.WAFERSORT_DATE_INDEX && rowWithCells[LotYieldConstants.WAFERSORT_DATE_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getWaferSortDate() != null )
                        {
                            writableCellFormat = new WritableCellFormat( dateFormat );
                            writableCellFormat.setVerticalAlignment( jxl.format.VerticalAlignment.CENTRE );
                            writableCellFormat.setAlignment( jxl.format.Alignment.CENTRE );
                            date = new DateTime( LotYieldConstants.WAFERSORT_DATE_INDEX, currentRowNo, sdf.parse( lotYieldInfo.getWaferSortDate() ), writableCellFormat );
                            writableSheet.addCell( date );
                        }
                        if( rowWithCells.length >= LotYieldConstants.WAFERSORT_TEST_PGM_INDEX && rowWithCells[LotYieldConstants.WAFERSORT_TEST_PGM_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            label = new Label( LotYieldConstants.WAFERSORT_TEST_PGM_INDEX, currentRowNo, lotYieldInfo.getWaferSortTestPgm() );
                            writableSheet.addCell( label );
                        }
                        if( rowWithCells.length >= LotYieldConstants.TOTAL_GOOD_DIE_QTY_INDEX && rowWithCells[LotYieldConstants.TOTAL_GOOD_DIE_QTY_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getTotalGoodDieQty() > 0 )
                        {
                            number = new Number( LotYieldConstants.TOTAL_GOOD_DIE_QTY_INDEX, currentRowNo, lotYieldInfo.getTotalGoodDieQty() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.WAFERSORT_YILED_INDEX && rowWithCells[LotYieldConstants.WAFERSORT_YILED_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            //writableCell = writableSheet.getWritableCell( LotYieldConstants.WAFERSORT_YILED_INDEX, ( currentRowNo ) );

                            //number = ( Number ) writableCell;
                            //number.setValue( lotYieldInfo.getWaferSortYield() );
                        }
                        if( rowWithCells.length >= LotYieldConstants.MARKING_INDEX && rowWithCells[LotYieldConstants.MARKING_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            writableCellFormat = new WritableCellFormat( new WritableFont(WritableFont.ARIAL, 10) );
                            writableCellFormat.setWrap( true );
                            label = new Label( LotYieldConstants.MARKING_INDEX, currentRowNo, lotYieldInfo.getMarking(), writableCellFormat );
                            writableSheet.addCell( label );
                        }
                        if( rowWithCells.length >= LotYieldConstants.ASSEMBLY_LOT_NOS_INDEX && rowWithCells[LotYieldConstants.ASSEMBLY_LOT_NOS_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            label = new Label( LotYieldConstants.ASSEMBLY_LOT_NOS_INDEX, currentRowNo, lotYieldInfo.getAssemblyLotNo() );
                            writableSheet.addCell( label );
                        }
                        if( rowWithCells.length >= LotYieldConstants.ASSEMBLY_IN_QTY_INDEX && rowWithCells[LotYieldConstants.ASSEMBLY_IN_QTY_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getAssemblyInQty() > 0 )
                        {
                            number = new Number( LotYieldConstants.ASSEMBLY_IN_QTY_INDEX, currentRowNo, lotYieldInfo.getAssemblyInQty() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.ASSEMBLY_YIELD_INDEX && rowWithCells[LotYieldConstants.ASSEMBLY_YIELD_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            //writableCell = writableSheet.getWritableCell( LotYieldConstants.ASSEMBLY_YIELD_INDEX, ( currentRowNo ) );

                            //number = ( Number ) writableCell;
                            //number.setValue( lotYieldInfo.getAssemblyYield() );
                        }
                        if( rowWithCells.length >= LotYieldConstants.TEST_QTY_IN_INDEX && rowWithCells[LotYieldConstants.TEST_QTY_IN_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getTestQtyIn() > 0 )
                        {
                            number = new Number( LotYieldConstants.TEST_QTY_IN_INDEX, currentRowNo, lotYieldInfo.getTestQtyIn() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.TEST_DATE_IN_INDEX && rowWithCells[LotYieldConstants.TEST_DATE_IN_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getTestDateIn() != null )
                        {
                            writableCellFormat = new WritableCellFormat( dateFormat );
                            writableCellFormat.setVerticalAlignment( jxl.format.VerticalAlignment.CENTRE );
                            writableCellFormat.setAlignment( jxl.format.Alignment.CENTRE );
                            date = new DateTime( LotYieldConstants.TEST_DATE_IN_INDEX, currentRowNo, StringUtils.format( lotYieldInfo.getTestDateIn(), "mm/dd/yy"), writableCellFormat, DateTime.GMT );
                            writableSheet.addCell( date );
                        }
                        if( rowWithCells.length >= LotYieldConstants.BIN1_INDEX && rowWithCells[LotYieldConstants.BIN1_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getBin1() > 0 )
                        {
                            number = new Number( LotYieldConstants.BIN1_INDEX, currentRowNo, lotYieldInfo.getBin1() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.BIN2_INDEX && rowWithCells[LotYieldConstants.BIN2_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getBin2() > 0  )
                        {
                            number = new Number( LotYieldConstants.BIN2_INDEX, currentRowNo, lotYieldInfo.getBin2() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.BIN3_INDEX && rowWithCells[LotYieldConstants.BIN3_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getBin3() > 0  )
                        {
                            number = new Number( LotYieldConstants.BIN3_INDEX, currentRowNo, lotYieldInfo.getBin3() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.BIN4_INDEX && rowWithCells[LotYieldConstants.BIN4_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getBin4() > 0  )
                        {
                            number = new Number( LotYieldConstants.BIN4_INDEX, currentRowNo, lotYieldInfo.getBin4() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.BIN5_INDEX && rowWithCells[LotYieldConstants.BIN5_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getBin5() > 0  )
                        {
                            number = new Number( LotYieldConstants.BIN5_INDEX, currentRowNo, lotYieldInfo.getBin5() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.GOOD_UNITS_INDEX && rowWithCells[LotYieldConstants.GOOD_UNITS_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            //writableCell = writableSheet.getWritableCell( LotYieldConstants.GOOD_UNITS_INDEX, ( currentRowNo ) );

                            //number = ( Number ) writableCell;
                            //number.setValue( lotYieldInfo.getGoodUnits() );
                        }
                        if( rowWithCells.length >= LotYieldConstants.UNCONUT_UNITS_INDEX && rowWithCells[LotYieldConstants.UNCONUT_UNITS_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            //writableCell = writableSheet.getWritableCell( LotYieldConstants.UNCONUT_UNITS_INDEX, ( currentRowNo ) );

                            //number = ( Number ) writableCell;
                            //number.setValue( lotYieldInfo.getUnCountUnits() );
                        }
                        if( rowWithCells.length >= LotYieldConstants.TEST_YIELD_INDEX && rowWithCells[LotYieldConstants.TEST_YIELD_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            /*writableCell = writableSheet.getWritableCell( LotYieldConstants.TEST_YIELD_INDEX, ( currentRowNo ) );

                            number = ( Number ) writableCell;
                            number.setValue( lotYieldInfo.getTestYield() );*/
                        }
                        if( rowWithCells.length >= LotYieldConstants.TESTER_INDEX && rowWithCells[LotYieldConstants.TESTER_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            label = new Label( LotYieldConstants.TESTER_INDEX, currentRowNo, lotYieldInfo.getTester() );
                            writableSheet.addCell( label );
                        }
                        if( rowWithCells.length >= LotYieldConstants.TEST_PGM_INDEX && rowWithCells[LotYieldConstants.TEST_PGM_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            //writableCell = writableSheet.getWritableCell( LotYieldConstants.TEST_PGM_INDEX, ( currentRowNo ) );

                            //label = ( Label ) writableCell;
                            //label.setString( lotYieldInfo.getTestPgm() );
                        }
                        if( rowWithCells.length >= LotYieldConstants.SCAN_OUTQTY_INDEX && rowWithCells[LotYieldConstants.SCAN_OUTQTY_INDEX].getType().equals( CellType.EMPTY ) && lotYieldInfo.getScanOutQty() > 0 )
                        {
                            number = new Number( LotYieldConstants.SCAN_OUTQTY_INDEX, currentRowNo, lotYieldInfo.getScanOutQty() );
                            writableSheet.addCell( number );
                        }
                        if( rowWithCells.length >= LotYieldConstants.SCAN_YIELD_INDEX && rowWithCells[LotYieldConstants.SCAN_YIELD_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            /*writableCell = writableSheet.getWritableCell( LotYieldConstants.SCAN_YIELD_INDEX, ( currentRowNo ) );

                            number = ( Number ) writableCell;
                            number.setValue( lotYieldInfo.getScanYield() );*/
                        }
                        if( rowWithCells.length >= LotYieldConstants.CUM_YIELD_INDEX && rowWithCells[LotYieldConstants.CUM_YIELD_INDEX].getType().equals( CellType.EMPTY ) )
                        {
                            /*writableCell = writableSheet.getWritableCell( LotYieldConstants.CUM_YIELD_INDEX, ( currentRowNo ) );

                            number = ( Number ) writableCell;
                            number.setValue( lotYieldInfo.getCummYield() );*/
                        }
                    }
                }
            }

            writableWorkbook.write();
            writableWorkbook.close();
            sourceWorkbook.close();

            writableWorkbook = null;
            sourceWorkbook = null;

            //System.out.println ( "---------------Done--------------" );

        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Problem in uploading the information into Cummulative Yield Report File" );
        }
        finally
        {
            if( writableWorkbook != null )
            {
                try
                {
                    writableWorkbook.write();
                    writableWorkbook.close();
                    writableWorkbook = null;
                }
                catch ( Exception e) { e.printStackTrace(); }
            }
            if( sourceWorkbook != null )
            {
                try
                {
                    sourceWorkbook.close();
                    sourceWorkbook = null;
                }
                catch ( Exception e) { e.printStackTrace(); }
            }
            if( sourceFile != null )
            {
                try
                {
                    sourceFile = null;
                }
                catch ( Exception e ) { e.printStackTrace(); }
            }
        }
    }

    private static ArrayList loadExcelLotDetails( String baseProductName ) throws ScalarException
    {
        ExcelLotInfo excelLotInfo   = null;
        File inputWorkbook          = null;
        Workbook workbook           = null;
        ArrayList rowsList          = new ArrayList();
        int startingRow             = 0;
        int endingRow               = 0;
        String lotNumber            = "";
        String prvLotNumber         = "";
        String nextRowLotNumber     = "";
        AttachFile attachedFile     = null;
        try
        {
            attachedFile = retrieveAttachedFileInformation( baseProductName );
            inputWorkbook = new File( attachedFile.getFileNameWithPath() );
            workbook  = Workbook.getWorkbook( inputWorkbook );

            Sheet sheet2 = workbook.getSheet(0);
            int columns = sheet2.getColumns();
            int rows = sheet2.getRows();
            int n = 50;

            //System.out.println("work book columns = " + columns + " rows = " + rows + "\n");

            for( int row = 1; row < rows; row++ )
            {
                endingRow = 0;
                startingRow = 0;
                if( row == ( rows - 1 ) )
                {
                    nextRowLotNumber = "";
                }
                else
                {
                    nextRowLotNumber = sheet2.getCell( 0, ( row + 1 ) ).getContents();
                }

                if( "".equalsIgnoreCase( sheet2.getCell( 0, row ).getContents() /*Lot No*/ ) &&  "".equalsIgnoreCase( nextRowLotNumber ) && ( row < ( rows - 1 ) /*Dont Skip Last Row*/) )
                {
                    prvLotNumber = excelLotInfo.getLotNumber();
                    continue;
                }

                lotNumber = sheet2.getCell( 0, row ).getContents();

                if( "".equalsIgnoreCase( lotNumber ) == false  && lotNumber.equalsIgnoreCase( prvLotNumber ) == false )
                {
                    excelLotInfo = new com.scalar.chiptrack.engineering.ExcelLotInfo();
                    excelLotInfo.setStartingRow( row );
                    excelLotInfo.setLotNumber( lotNumber );
                }


                //System.out.println(" row = " + row + "    Lot Number = " + lotNumber + " prv LotNumber = " + prvLotNumber + " nextRowLotNumber = " + nextRowLotNumber );
                //if( ( "".equalsIgnoreCase( nextRowLotNumber ) == false  && excelLotInfo.getLotNumber().equalsIgnoreCase( nextRowLotNumber ) == false ) || ( row == ( rows - 1 ) ) )
                if(  "".equalsIgnoreCase( nextRowLotNumber ) == false  || ( row == ( rows - 1 ) /*if current row is last row then insert the information*/) )
                {
                    excelLotInfo.setEndingRow( row );
                    rowsList.add( excelLotInfo );

                    //System.out.println(" row = " + row + "    Lot Number = " + excelLotInfo.getLotNumber() + " Starting Row No = " + excelLotInfo.getStartingRow() + " Ending Row No = " + excelLotInfo.getEndingRow() );
                }

                nextRowLotNumber = "";
                prvLotNumber = lotNumber;
            }
            workbook.close();
            //copy.close();

            workbook = null;
            //copy = null;
        }
        catch( IOException e )
        {
            e.printStackTrace();
        }
        catch( BiffException e )
        {
            e.printStackTrace();
        }
        finally
        {
            if( workbook != null )
            {
                workbook.close();
                workbook = null;
            }
            if( inputWorkbook != null )
            {
                try
                {
                    inputWorkbook = null;
                }
                catch ( Exception e ) { e.printStackTrace(); }
            }
        }

        return rowsList;
    }

    private static void mergetNewLots( HashMap lotYieldInfoMap, ArrayList rowsList )
    {
        ArrayList mergingList           = new ArrayList();
        Iterator lotYieldInfoIterator   = lotYieldInfoMap.keySet().iterator();
        LotYieldInfo lotYieldInfo       = null;
        com.scalar.chiptrack.engineering.ExcelLotInfo excelLotInfoRow    = null;
        ExcelLotInfo excelLotInfo       = null;
        int rowsListSize                = rowsList.size();
        boolean isLotFound              = false;
        String lotNumber                = null;

        while( lotYieldInfoIterator.hasNext() )
        {
            lotNumber = ( String ) lotYieldInfoIterator.next();
            lotYieldInfo = ( LotYieldInfo ) lotYieldInfoMap.get( lotNumber );

            for( int i = 0; i < rowsListSize; i++ )
            {
                excelLotInfoRow = ( ExcelLotInfo ) rowsList.get(i);

                if( lotYieldInfo.getWaferLotNo().equalsIgnoreCase( excelLotInfoRow.getLotNumber() ) )
                {
                    isLotFound = true;
                    break;
                }
            }

            if( isLotFound == false )
            {
                excelLotInfo = new ExcelLotInfo();

                excelLotInfo.setNewLotNumber( true );
                excelLotInfo.setLotNumber( lotYieldInfo.getWaferLotNo() );

                excelLotInfo.setLotYieldInfo( lotYieldInfo );

                mergingList.add( excelLotInfo );
            }
            isLotFound = false;
        }

        if( mergingList != null && mergingList.size() > 0 && rowsList != null && rowsList.size() > 0 )
        {
            rowsList.addAll( mergingList );
        }
    }

}
